Course: Advanced Excel
Topic: Building Loan Repayment and EMI Models
Course Link: Advanced Excel Course – JustAcademy
Demo Link: Register for a Course Demo
Official Website: JustAcademy Homepage
Loan repayment and Equated Monthly Instalment (EMI) models are important applications of Advanced Excel in banking, financial planning, accounting, business analysis, and personal finance. Excel can calculate monthly instalments, total interest, total repayment, outstanding balances, and the allocation of each payment between principal and interest.
An EMI is a fixed periodic payment made by a borrower to repay a loan over an agreed period. For a standard loan with a fixed interest rate and monthly payments, each EMI generally contains two components: interest on the outstanding principal and repayment of part of the principal.
As the outstanding balance decreases, the interest portion usually becomes smaller while the principal repayment portion becomes larger. Excel financial functions and formulas help build a model that explains this process clearly.
| Term | Meaning | Example |
|---|---|---|
| Principal | The original amount borrowed from the lender. | ₹10,00,000 |
| Annual Interest Rate | The yearly interest rate charged on the outstanding loan balance. | 9% per year |
| Monthly Interest Rate | The periodic rate used when payments are monthly. | 9% / 12 = 0.75% per month |
| Loan Tenure | The agreed duration for repaying the loan. | 5 years |
| Number of Payments | The total number of instalments during the loan tenure. | 5 × 12 = 60 payments |
| EMI | The regular monthly payment containing principal and interest. | Calculated using PMT |
| Interest Component | The amount of the payment allocated to interest for a period. | Opening balance × monthly rate |
| Principal Component | The amount of the payment that reduces the loan balance. | EMI − interest |
| Outstanding Balance | The unpaid loan principal remaining after a payment. | Opening balance − principal repaid |
| Total Interest | The sum of all interest payments over the loan term. | Total repayment − principal |
For a standard loan with a fixed periodic interest rate and equal payments, the EMI can be calculated using the following formula:
EMI = P × r × (1 + r)n / ((1 + r)n − 1)
For monthly payments, the annual nominal interest rate is commonly divided by 12, and the tenure in years is multiplied by 12. The assumptions must match the lender's actual loan terms.
Suppose a borrower takes a loan of ₹10,00,000 at an annual interest rate of 9% for 5 years, with monthly repayments.
The EMI is approximately ₹20,758 per month. The exact displayed amount may vary slightly with rounding and the assumptions used by the lender.
First, create an input section for the loan information. Keep the input cells separate from the calculation area so that the model is easy to update.
| Cell | Description | Sample Value |
|---|---|---|
| B2 | Loan Amount | 1000000 |
| B3 | Annual Interest Rate | 9% |
| B4 | Loan Tenure in Years | 5 |
| B5 | Payments per Year | 12 |
| B6 | Monthly Interest Rate | =B3/B5 |
| B7 | Total Number of Payments | =B4*B5 |
| B8 | Monthly EMI | =-PMT(B6,B7,B2) |
| B9 | Total Repayment | =B8*B7 |
| B10 | Total Interest | =B9-B2 |
The PMT function calculates the periodic payment required for a loan when the periodic interest rate and payment schedule are fixed. It is one of the most useful Excel financial functions for building an EMI model.
Syntax:
=PMT(rate, nper, pv, [fv], [type])| Argument | Description |
|---|---|
| rate | Interest rate for each payment period. |
| nper | Total number of payment periods. |
| pv | Present value, normally the loan principal. |
| fv | Optional future balance after the final payment; normally 0 for a fully repaid loan. |
| type | Optional payment timing: 0 for end of period or 1 for beginning of period. |
=-PMT(9%/12,5*12,1000000)The minus sign displays the EMI as a positive amount. Excel's financial functions follow a cash-flow sign convention: money received and money paid out are represented with opposite signs.
A repayment schedule shows how the loan balance changes after every instalment. It records the opening balance, EMI, interest, principal repayment, and closing balance for each month.
| Column | Heading | Purpose |
|---|---|---|
| A | Payment Number | Identifies each monthly instalment. |
| B | Opening Balance | Loan balance at the start of the month. |
| C | EMI | Regular monthly instalment. |
| D | Interest Payment | Interest charged for the month. |
| E | Principal Payment | Amount of EMI reducing principal. |
| F | Closing Balance | Balance remaining after payment. |
Assume the loan inputs are stored in the cells described in the earlier section. Place the repayment schedule headings in row 13 and the first payment details in row 14.
| Cell | Formula | Explanation |
|---|---|---|
| A14 | =1 |
First payment number. |
| B14 | =$B$2 |
Opening balance equals the original principal. |
| C14 | =$B$8 |
Monthly EMI linked to the calculator. |
| D14 | =B14*$B$6 |
Interest on the opening balance. |
| E14 | =MIN(B14,C14-D14) |
Principal repaid after accounting for interest. |
| F14 | =MAX(0,B14-E14) |
Closing balance after the payment. |
In row 15, enter the following formulas and fill them down for the remaining instalments.
| Cell | Formula |
|---|---|
| A15 | =A14+1 |
| B15 | =F14 |
| C15 | =MIN($B$8,B15+D15) |
| D15 | =B15*$B$6 |
| E15 | =MIN(B15,C15-D15) |
| F15 | =MAX(0,B15-E15) |
Note: In the next-month EMI formula above, D15 refers to the interest for the current row. Since Excel calculates formulas using cell references, a simpler and clearer schedule is usually to link each month's EMI directly to the fixed EMI cell: =$B$8. For the next row, use =$B$8 in C15 and fill down. This avoids unnecessary dependencies between the EMI and interest calculations.
For a straightforward fixed-rate loan, use these formulas in each payment row:
=$B$8.=B15*$B$6.=MIN(B15,C15-D15).=MAX(0,B15-E15).Fill the formulas down for the number of payments in B7. In a production model, the final instalment may need adjustment for rounding so that the balance becomes exactly zero without a negative closing balance.
Excel provides separate financial functions for the interest and principal components of a fixed loan payment.
IPMT calculates the interest portion of a payment for a specified period.
=-IPMT($B$6,A14,$B$7,$B$2)This formula returns the interest component for the payment number in A14 as a positive amount.
PPMT calculates the principal portion of a payment for a specified period.
=-PPMT($B$6,A14,$B$7,$B$2)This formula returns the principal component for the payment number in A14 as a positive amount.
For a standard fixed-rate loan, the principal and interest portions should add up to the EMI, subject to rounding.
Principal Component + Interest Component = EMIFor example, if a monthly EMI is ₹20,758 and the first month's interest is ₹7,500, the principal component is approximately ₹13,258.
Once the EMI is calculated, Excel can estimate the total amount paid and the total interest cost over the loan term.
| Metric | Formula | Purpose |
|---|---|---|
| Total Repayment | =B8*B7 |
Estimated total of all instalments. |
| Total Interest | =B9-B2 |
Estimated interest over the full loan term. |
| Total Principal Repaid | =SUM(E14:E73) |
Sum of principal payments for a 60-month schedule. |
| Total Interest from Schedule | =SUM(D14:D73) |
Sum of scheduled monthly interest payments. |
| Remaining Balance | =F73 |
Closing balance after payment 60. |
Adjust the schedule range to match the actual loan tenure. For a five-year loan with 60 monthly payments, rows 14 through 73 contain 60 payment rows if the first payment starts in row 14.
| Function | Purpose | Example |
|---|---|---|
| PMT | Calculates a fixed periodic loan payment. | =-PMT(B6,B7,B2) |
| IPMT | Calculates the interest portion for a specific period. | =-IPMT(B6,1,B7,B2) |
| PPMT | Calculates the principal portion for a specific period. | =-PPMT(B6,1,B7,B2) |
| NPER | Estimates the number of periods needed for a loan or payment plan. | =NPER(B6,-B8,B2) |
| RATE | Estimates the periodic interest rate for a loan with known cash flows. | =RATE(B7,-B8,B2)*12 |
| PV | Calculates present value based on a periodic rate and payment stream. | =PV(B6,B7,-B8) |
Financial functions use cash-flow sign conventions. If a formula returns a negative number when a positive display is desired, review the signs of the inputs before adding or removing a minus sign.
Loan tenure and interest rate have a direct effect on EMI and total interest. A longer tenure usually lowers the monthly EMI but increases the total interest paid. A higher interest rate generally increases both EMI and the overall borrowing cost.
Use a loan amount of ₹10,00,000 and an annual nominal interest rate of 9%. Create a table with different loan tenures and calculate the EMI for each one.
| Tenure | Total Payments | Excel Formula | Typical Effect |
|---|---|---|---|
| 3 years | 36 | =-PMT(9%/12,3*12,1000000) |
Higher EMI, lower total interest. |
| 5 years | 60 | =-PMT(9%/12,5*12,1000000) |
Moderate EMI and total interest. |
| 7 years | 84 | =-PMT(9%/12,7*12,1000000) |
Lower EMI, higher total interest. |
| 10 years | 120 | =-PMT(9%/12,10*12,1000000) |
Lower monthly payment, longer repayment period. |
Borrowers sometimes make additional principal repayments to reduce their outstanding balance. An Excel model can estimate the effect of these payments, but the treatment depends on the lender's rules. Some lenders reduce the tenure while others recalculate the EMI.
For a simplified model, add an extra principal payment column to the schedule.
| Column | Heading | Example Formula |
|---|---|---|
| G | Extra Principal Payment | Enter a user-specified amount, such as 5000. |
| H | Total Principal Paid | =MIN(B14,E14+G14) |
| I | Revised Closing Balance | =MAX(0,B14-H14) |
For an accurate prepayment model, ensure that the regular principal amount is recalculated from the actual EMI and interest, and cap the total principal payment at the outstanding balance. Also account for any prepayment fees or lender-specific rules. Do not simply subtract an extra payment from the balance without reconciling the principal and interest calculations.
Charts make it easier to explain how a loan is repaid over time.
Create a small summary area showing the original principal, monthly EMI, total repayment, total interest, and final outstanding balance. These figures give the user a quick view of the loan's cost.
Data validation helps prevent incorrect inputs from damaging the model.
| Problem | Possible Cause | Solution |
|---|---|---|
| EMI is negative | Excel is following its cash-flow sign convention. | Use =-PMT(rate,nper,pv) if a positive display is desired. |
| EMI is much too high | Annual interest rate may have been used as a monthly rate. | For a nominal annual rate with monthly payments, divide by 12. |
| EMI is much too low | The total number of payments may be incorrect. | For monthly payments, multiply the number of years by 12. |
| Closing balance becomes negative | Rounding or the final payment exceeds the remaining balance. | Cap the final principal payment at the outstanding balance and adjust the last payment if necessary. |
| Final balance is not zero | Some periods may be missing or formulas may have incorrect references. | Verify the number of payment rows and check the final-period calculation. |
| Interest is identical every month | The formula may refer to the original loan amount instead of the current opening balance. | Calculate interest using each period's opening balance. |
| Formula returns an error | Inputs may be blank, text, or invalid. | Check cell types and use validation to restrict inputs. |
| Calculated EMI differs from the lender's quote | Fees, rate conventions, payment dates, or rounding rules may differ. | Compare the model assumptions with the lender's repayment terms. |
$B$2 and $B$6.Exercise: Build an EMI calculator for a loan of ₹7,50,000 at an annual interest rate of 10.5% for 4 years.
Create a professional dashboard that allows a user to compare different loan scenarios. The dashboard should have an input panel, key financial metrics, a repayment schedule, and visualisations.
By completing this project, learners will practise financial formulas, relative and absolute references, formula auditing, What-If Analysis, conditional formatting, and chart creation in Excel.
Answer: EMI stands for Equated Monthly Instalment. It is a regular monthly payment that generally includes both principal repayment and interest.
Answer: The PMT function calculates the periodic payment using the interest rate per period, number of payments, and present value of the loan.
Answer: For a loan model using a nominal annual rate and monthly payment periods, dividing by 12 converts the annual rate into a monthly periodic rate. The method should match the lender's rate convention.
Answer: Excel uses a cash-flow sign convention. The loan principal is money received, while instalments are money paid out. A minus sign can be used to display the payment as a positive number.
Answer: IPMT calculates the interest portion of a specified payment period, while PPMT calculates the principal portion.
Answer: It is a period-by-period table showing the opening loan balance, payment, interest, principal repaid, and closing balance.
Answer: A longer tenure usually reduces the monthly EMI but increases the total interest paid, assuming other factors remain the same.
Answer: It confirms that the scheduled payments repay the loan as expected and helps identify missing periods, incorrect formulas, and rounding differences.
Answer: No. PMT calculates the payment based on the specified loan principal, rate, and payment periods. Additional fees and charges must be modelled separately.
Answer: Excel can use separate formulas, What-If Analysis, one-variable or two-variable Data Tables, and charts to compare EMI and total interest under different rates and tenures.
Building loan repayment and EMI models is a practical Advanced Excel skill for financial analysis, budgeting, and business reporting. By combining PMT, IPMT, PPMT, cell references, repayment schedules, and What-If Analysis, learners can build a flexible model that explains how payments are allocated and how loan balances change over time.
A well-designed model should be accurate, transparent, easy to update, and clear about its assumptions. For actual borrowing decisions, always verify calculations against the lender's repayment schedule and applicable loan terms.
